﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Xml;
using ExcelDataReader;
using SdmxMl.SmsImportCfg;
using SdmxMl.Common;
using SdmxMl.Structure;
using SdmxMl.Manager;

namespace SdmxMl.Excel
{
    public class POP_Import
    {
        private ExcelDataReader.ExcelDataReader spreadsheetConcept = null;
        private ExcelDataReader.ExcelDataReader spreadsheetCode = null;
        private ConceptSchemeType cst;
        private SmManager sm;

        private ExcelDataReader.ExcelDataReader LoadSources(string propertyPath)
        {
            ExcelDataReader.ExcelDataReader spreadsheet = null;
            if (propertyPath == null)
                throw new ApplicationException("Please provides path.");

            FileStream fs = null;
            try
            {
                fs = new FileStream(propertyPath, FileMode.Open, FileAccess.Read, FileShare.Read);
                spreadsheet = new ExcelDataReader.ExcelDataReader(fs);
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }

            return spreadsheet;
        }

        public POP_Import()
        {
            spreadsheetConcept = LoadSources(@"H:\AAAA\Codelist_RE_desc.xls");
            spreadsheetCode = LoadSources(@"H:\AAAA\Codelist_RE_Values_desc.xls");
        }

        public SmManager LoadToSdmx()
        {
            // Create a single conceptSceme
            cst = new ConceptSchemeType();
            cst.Id = "CS_RE";
            cst.AgencyId = "OFS_BS_POP_GRP";
            cst.ValidFrom = "2010-12-31";
            cst.Name.UpdateDefaultText("Register Erhebung");

            sm = new SmManager();
            sm.PutArtefact(SdmxArtefactType.Concepts, cst);

            // Must be located unique worksheet
            DataTable dt = spreadsheetConcept.WorkbookData.Tables[0];
            WorkSheetHelper wh = new WorkSheetHelper(dt);

            for (int row = 1; row <= dt.Rows.Count; row++)
            {
                if (row > 1) // 1 header rows
                {
                    CreateConcept(row, wh);
                }
            }
            // now sort codelists
            foreach (CodeListType clt in sm.GetArtefactManager(SdmxArtefactType.CodeLists).ArtefactList)
                clt.CodeList.Sort(delegate(ISdmxHierarchyListItem p1, ISdmxHierarchyListItem p2)
                { return Convert.ToInt32(p1.Id) - Convert.ToInt32(p2.Id); });

            bool harmo = true;
            if (harmo)
            {
                // Now create quality concept for codelist with quality codes (negativ)
                // and create a quality codelist that take nég codes
                List<ConceptType> lc = new List<ConceptType>(64);
                foreach (ConceptType c in cst.ConceptList)
                {
                    if (c.CoreRepresentation.IsFilled)
                    {
                        CreateQualityCodelist(c, lc);
                    }
                }
                foreach (ConceptType ct in lc)
                    cst.ConceptList.Add(ct);
            }

            // now sort concepts
            cst.ConceptList.Sort(delegate(ISdmxHierarchyListItem p1, ISdmxHierarchyListItem p2)
            { return p1.Id.CompareTo(p2.Id); });



            return sm;
        }

        private void CreateQualityCodelist(ConceptType c, List<ConceptType> listConcept)
        {
            CodeListType clt = sm.GetArtefact(SdmxArtefactType.CodeLists, c.CoreRepresentation) as CodeListType;
            if (clt != null && clt.CodeList.Count > 0 && Convert.ToInt32(clt.CodeList[0].Id) < 0)
            {
                // Create quality codelist
                CodeListType cltQuality = new CodeListType();
                cltQuality.Id = "CL_FLG_" + c.Id;
                cltQuality.AgencyId = clt.AgencyId;
                cltQuality.Name.UpdateDefaultText("Quality Flags for " + c.Id);
                cltQuality.CodeList.HasHierarchy = false;
                List<CodeType> lc = new List<CodeType>();
                foreach (CodeType code in clt.CodeList)
                {
                    // transfer quality codes
                    if (Convert.ToInt32(code.Id) < 0)
                    {
                        cltQuality.CodeList.Add(code);
                        lc.Add(code);
                    }
                    else
                        break;
                }
                foreach (CodeType cq in lc)
                    clt.CodeList.Remove(cq);

                if (clt.CodeList.Count == 0)
                {
                    sm.GetArtefactManager(SdmxArtefactType.CodeLists).ArtefactList.Remove(clt);
                    c.CoreRepresentation.Id = string.Empty;
                    c.CoreRepresentation.AgencyId = string.Empty;

                }
                // Check if same already exists
                if (cltQuality != null && cltQuality.CodeList.Count > 0)
                {
                    CodeListType cltAlreadyDefined = CodelistAlreadyDefined(cltQuality, true);
                    if (cltAlreadyDefined != null)
                    {
                        cltQuality = cltAlreadyDefined;
                        cltQuality.Name.UpdateDefaultText(cltQuality.Name.ToString() + ", " + c.Id);
                    }
                    else
                        sm.PutArtefact(SdmxArtefactType.CodeLists, cltQuality);

                    // create quality concept
                    ConceptType ct = new ConceptType();
                    ct.Id = c.Id + "_F";
                    ct.Name.UpdateDefaultText("Flag for " + c.Id);
                    // refere codelist in concept
                    ct.CoreRepresentation.Id = cltQuality.Id;
                    ct.CoreRepresentation.AgencyId = cltQuality.AgencyId;
                    listConcept.Add(ct);
                }

            }
        }

        private void CreateConcept(int row, WorkSheetHelper wh)
        {
            string id = wh.GetCellContent(row, 2);
            id = id.Replace(" ", "");
            ConceptType c = cst.ConceptList.LocateItem(id) as ConceptType;
            string lang = wh.GetCellContent(row, 4).ToLower();
            if (c == null)
            {
                c = new ConceptType();
                c.Id = id;

                // Codelist reference
                string type = wh.GetCellContent(row, 5);
                if (type == "CL")
                {
                    CreateCodelist(c);
                }
                cst.ConceptList.Add(c);
            }
            // Update name for language
            c.Name.UpdateForLanguage(lang, wh.GetCellContent(row, 7));
        }

        private void CreateCodelist(ConceptType c)
        {
            // Must be located unique worksheet
            DataTable dt = spreadsheetCode.WorkbookData.Tables[0];
            WorkSheetHelper wh = new WorkSheetHelper(dt);
            CodeListType clt = null;

            for (int row = 1; row <= dt.Rows.Count; row++)
            {
                if (row > 1) // 1 header rows
                {
                    string id = wh.GetCellContent(row, 2);
                    id = id.Replace(" ", "");
                    if (id == c.Id)
                    {
                        if (clt == null)
                        {
                            clt = new CodeListType();
                            clt.CodeList.HasHierarchy = false;
                            clt.Id = "CL_" + c.Id.ToUpper();
                            clt.AgencyId = "OFS_BS_POP_GRP";
                            clt.Name.UpdateDefaultText("Codelist for " + c.Id);

                            // refere codelist in concept
                            c.CoreRepresentation.Id = clt.Id;
                            c.CoreRepresentation.AgencyId = clt.AgencyId;
                        }

                        string codeId = wh.GetCellContent(row, 4);
                        CodeType code = clt.CodeList.LocateItem(codeId) as CodeType;
                        string lang = wh.GetCellContent(row, 3).ToLower();
                        if (code == null)
                        {
                            code = new CodeType();
                            code.Id = codeId;
                            clt.CodeList.Add(code);
                        }
                        // Update name for language
                        code.Name.UpdateForLanguage(lang, wh.GetCellContent(row, 10));
                    }
                }
            }

            // Codelist without codes (was only quality)
            if (clt != null)
            {
                if (clt.CodeList.Count == 0)
                {
                    c.CoreRepresentation.Id = string.Empty;
                    c.CoreRepresentation.AgencyId = string.Empty;
                }
                else
                {
                // Check if same already exists
                    CodeListType cltAlreadyDefined = CodelistAlreadyDefined(clt, false);
                    if (cltAlreadyDefined != null)
                    {
                        clt = cltAlreadyDefined;
                        clt.Name.UpdateDefaultText(clt.Name.ToString() + ", " + c.Id);
                        c.CoreRepresentation.Id = clt.Id;
                        c.CoreRepresentation.AgencyId = clt.AgencyId;

                    }
                    else
                        sm.PutArtefact(SdmxArtefactType.CodeLists, clt);

                }
            }

            
        }


        private CodeListType CodelistAlreadyDefined(CodeListType newCodelist, bool onlyFlag)
        {
            ArtefactManager amgr = sm.GetArtefactManager(SdmxArtefactType.CodeLists);
            foreach (CodeListType clt in amgr.ArtefactList)
            {
                if (onlyFlag)
                {
                    if (clt.Id.StartsWith("CL_FLG_") && SameCodes(newCodelist.CodeList, clt.CodeList))
                        return clt;
                }
                else
                {
                    if (SameCodes(newCodelist.CodeList, clt.CodeList))
                        return clt;
                }
            }

            return null;
        }

        private bool SameCodes(CodeList a, CodeList b)
        {
            bool sameCodes = false;
            if (a.Count == b.Count)
            {
                sameCodes = true;
                for (int c = 0; c < a.Count; c++)
                {
                    if (a[c].Id != b[c].Id)
                    {
                        sameCodes = false;
                        break;
                    }
                    else
                    {
                        foreach (string lg in LangSupport.SupportedLanguages)
                        {
                            if (a[c].Name.GetForLanguage(lg) != b[c].Name.GetForLanguage(lg))
                            {
                                sameCodes = false;
                                break;
                            }
                        }
                        if (sameCodes == false)
                            break;
                    }
                }
            }

            return sameCodes;
        }

    }
}
